package www.lagou.app;

import javafx.scene.chart.PieChart;
import org.apache.commons.dbutils.QueryRunner;
import www.lagou.utils.DruidUtils;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class SQLcode1 {

    public static void main(String[] args) {

        // 1.创建核心类 获取连接对象
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = DruidUtils.getConnection();
            // 2.开启事务
            con.setAutoCommit(false);

            // 3.编写SQL
            String sql  = "update account set balance = balance - ? where card = ?";
            String sql2 = "update account set balance = balance + ? where card = ?";

            String sql3 = "select balance from account where card = ?";

            String sql4 = "insert into transaction values(null,?,?,?,?)";

            ps = con.prepareStatement(sql3);
            ps.setString(1,"1122334455");
            ResultSet rs = ps.executeQuery();
            rs.next();



            if (rs.getDouble("balance")>=5000){
                // 4.获取预处理对象 执行SQL
                ps = con.prepareStatement(sql);
                ps.setDouble(1,5000);
                ps.setString(2,"1122334455");
                ps.executeUpdate();

                //插入交易记录
                ps = con.prepareStatement(sql4);
                ps.setString(1,"1122334455");
                ps.setString(2,"转出");
                ps.setDouble(3,5000);
                ps.setString(4,"2021-3-25");
                ps.executeUpdate();


                ps = con.prepareStatement(sql2);
                ps.setDouble(1,5000);
                ps.setString(2,"55443332211");
                ps.executeUpdate();

                ps = con.prepareStatement(sql4);
                ps.setString(1,"55443332211");
                ps.setString(2,"转入");
                ps.setDouble(3,5000);
                ps.setString(4,"2021-3-25");
                ps.executeUpdate();

                con.commit();
                System.out.println("转账成功");
            }else {
                System.out.println("转账失败，余额不足");
                con.rollback();
            }
        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DruidUtils.close(con,ps);
        }
    }
}
